﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Windows.Forms;
using Microsoft.SharePoint.Client;
using System.IO;
using System.Drawing;
using System.Diagnostics;
using System.Data.OleDb;
using System.Runtime.Remoting.Contexts;
using Microsoft.SharePoint.Utilities;
using System.Globalization;
namespace SPMigrationOracleUtility
{
    class RunMappingComp
    {

        private string DataSource = "127.0.0.1";
        //private string UserId = "jtc";
        //private string Password = "tiger";
        private string UserId = "hr";
        private string Password = "pass@word1";
        private string strConResult;
        public string filePath { set; get; }
        public string MappingMode { set; get; }
        string strDt;
        string strDts;
        string tempDt;

        bool fileExists;
        bool status = false;
        string itemID;
        int MapFldCnt = 0;
        public string SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
        public string webURL { get; set; }
        public bool fldExist;
        bool filePrb = false;

        string _lmapName = "", _lsrcTbl = "", _ldstLst = "", _ltblFld = "", _llstFld = "", _litemUrl = "";
        ListViewItem lvi1;
        ListViewItem lvi2;
        ListViewItem lviMap;


        DataTable dtResults;
        bool chkDoc = false;
        bool chkLst = false;
        Hashtable ohtListName;
        Hashtable ohtfldMapping;
        Hashtable ohtDocMapping;
        ArrayList strArray = new ArrayList();
        MappingColumns mapColumns = new MappingColumns();

        public RunMappingComp()
        {
        }


        public ArrayList GetUniqueData(DataTable odt, string ColumnName)
        {
            ArrayList oarray = new ArrayList();
            oarray.Add("");
            return oarray;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="odt"></param>
        /// <param name="ColumnName"></param>
        /// <returns></returns>
        /// <summary>
        /// Getting Details from Mapping List and add that as checkbox in CheckedListBox
        /// </summary>
        /// <param name="chkLstBox">CheckedListBox to show the Mapping Names</param>
        /// 

        public void RetrieveMappingDetails(CheckedListBox chkLstBox,string migMode)
        {
            try
            {
                chkLstBox.DisplayMember = "MappingName";
                using (var clientContext = new ClientContext(SiteUrl))
                {
                    List list = clientContext.Web.Lists.GetByTitle("Mapping List");
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='MappingMode' /><Value Type='Choice'>" + migMode + "</Value></Eq></Where></Query></View>";
                    ListItemCollection collListItem = list.GetItems(camlQuery);
                    //CamlQuery query = CamlQuery.CreateAllItemsQuery();
                    //ListItemCollection listItems = list.GetItems(query);

                    clientContext.Load(collListItem);
                    clientContext.ExecuteQuery();
                    foreach (var Item in collListItem)
                    {
                        chkLstBox.Items.Add(new MappingListValues { MappingName = Convert.ToString(Item["Title"]), Description = Convert.ToString(Item["Description"]), Itemid = Convert.ToInt32(Item["ID"]), AllItems = Convert.ToString(Item["All_Items"]), Query = Convert.ToString(Item["Query"]), viewName = Convert.ToString(Item["View_Name"]), filePath = Convert.ToString(Item["ExcelFilePath"]) });
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            MappingMode = migMode;
        }

        /// <summary>
        /// Retrieve the Mapping Item Details by refering the Mapping List ID and the item details by using MappingColumns and HashTables
        /// </summary>
        /// <param name="oMapItem">MappingListValues for identify the Mapping Fields </param>
        /// 
        public bool RetrieveMappingItemDetails(MappingListValues oMapItem, ProgressBar progressBar, ListView lstView, ImageList imgLst, Label lblTimer, Stopwatch objWatch)
        {
            ohtListName = new Hashtable();
            ohtfldMapping = new Hashtable();
            ohtDocMapping = new Hashtable();
            chkDoc = false;
            chkLst = false;

            _lmapName = oMapItem.MappingName;
            lstView.View = System.Windows.Forms.View.Details;
            try
            {
                using (ClientContext ctx = new ClientContext(SiteUrl))
                {
                    List list = ctx.Web.Lists.GetByTitle("Mapping Column List");
                    CamlQuery query = new CamlQuery();
                    query.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID'/><Value Type='Text'>" + Convert.ToString(oMapItem.Itemid) + "</Value></Eq></Where></Query></View>";
                    ListItemCollection listItems = list.GetItems(query);                          
                    ctx.Load(listItems);
                    ctx.ExecuteQuery();
                    MapFldCnt = listItems.Count;
                    foreach (var lstItem in listItems)
                    {
                        lblTimer.Text = Convert.ToString(objWatch.Elapsed);
                        FieldUrlValue oweburlval = (FieldUrlValue)lstItem["Web_Url"];
                        webURL = oweburlval.Url;
                        mapColumns.srcTableName = Convert.ToString(lstItem["Source_Table_Name"]);
                        mapColumns.destListName = Convert.ToString(lstItem["Destination_List_Name"]);
                        mapColumns.ListType = Convert.ToString(lstItem["Destination_List_Template"]);
                        if (!ohtListName.ContainsKey(mapColumns.destListName))
                            ohtListName.Add(mapColumns.destListName, mapColumns.destListName);
                        if (string.Equals(mapColumns.ListType, "DocumentLibrary"))
                        {
                            chkDoc = true;
                            ohtDocMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]) + "." + Convert.ToString(lstItem["Destination_Field_Type"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"] + "." + Convert.ToString(lstItem["Source_Field_Type"])));
                           
                        }
                        else
                        {
                            chkLst = true;
                            ohtfldMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]) + "." + Convert.ToString(lstItem["Destination_Field_Type"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"] + "." + Convert.ToString(lstItem["Source_Field_Type"])));
                        }
                    }

                    if (string.IsNullOrEmpty(oMapItem.filePath))
                    {
                       // chkDoc = true;
                        dtResults = RetrieveData(mapColumns.srcTableName, Convert.ToString(oMapItem.Query), Convert.ToString(oMapItem.AllItems));

                    }
                    else
                    {
                       // chkDoc = false;
                        dtResults = RetrieveData(oMapItem.filePath);
                    }

                    Web oweb;
                    Uri SiteUri = new Uri(SiteUrl);
                    Uri webUri = new Uri(webURL);
                    if (SiteUri.AbsolutePath == webUri.AbsolutePath)
                    {
                        oweb = ctx.Web;
                    }
                    else
                    {
                        oweb = getWeb(webURL);
                    }

                    if (chkDoc)
                    {
                          MigrateToLibrary(dtResults, ohtDocMapping, ohtListName, webURL, progressBar, lstView);
                    }
                    else if (chkLst)
                    {
                        status = MigrateData(dtResults, ohtfldMapping, ohtListName, webURL, progressBar, lstView, imgLst, lblTimer, objWatch); 
                    }
                
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return status;
        }

        /// <summary>
        /// It Retrieves data from Oracle based on the TableName, Query and AllItems
        /// </summary>
        /// <param name="TableName">Table / View Name </param>
        /// <param name="Query">Query for the retrival </param>
        /// <param name="AllItems">Check whether user retrieve all Items</param>
        /// <returns>Returns the result as DataTable</returns>
        /// 

        public DataTable RetrieveData(string TableName, string Query, string AllItems)
        {
            _lsrcTbl = TableName;
            DataTable qryResult = new DataTable();
            strConResult = orcConnection.Connect(DataSource, UserId, Password);
            string qry = HtmlRemoval.StripTagsRegex(Query);
            qry = HtmlRemoval.StripTagsRegex1(qry);
            try
            {
                if (AllItems == "True")
                {
                    if (string.IsNullOrEmpty(qry))
                    {
                        // Get Table Name
                        
                        qryResult = orcConnection.GetObjectData(TableName);
                    }
                    else
                    {
                        if (qry.Length > 0)
                        {
                            // Get Table Name
                            qryResult = orcConnection.GetObjectData(TableName);
                        }
                        else
                        {
                            // get view name
                            qryResult = orcConnection.GetObjectData(TableName);
                        }
                    }
                    return qryResult;
                }
                else
                {
                    //pass the query
                    qryResult = orcConnection.ExecuteSql(qry);
                    return qryResult;
                }
            }
            catch (Exception)
            {
                throw;
            }
           
        }

        // for excel Migration

        public DataTable RetrieveData(string excelFileName)
        {
            
            FileHelper objHelper = new FileHelper(excelFileName);

            try
            {
                string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""", excelFileName);
                string query = String.Format("select * from [{0}$]", "Sheet1");
                using (OleDbConnection con = new OleDbConnection(connectionString))
                {
                    con.Open();
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
                    DataTable dt = new DataTable();
                    dataAdapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
           
        }

        /// <summary>
        /// Migrate the records in the SharePoint List
        /// </summary>
        /// <param name="oResultData">Oracle Table Result Set</param>
        /// <param name="oColMapping">Mapping Field Details</param>
        /// <param name="oListMapping">Mapping List Details</param>
        /// <param name="Weburl">Site Url</param>
        /// 

        private static String ToSPDate(String strDt)
        {
            if (strDt == String.Empty)
                return strDt;
            else
                return (Convert.ToDateTime(strDt)).ToString("yyyy-MM-dd");
        }
        private static String ToSPDateTime(String strDt)
        {
            if (strDt == String.Empty)
                return strDt;
            else
                return (Convert.ToDateTime(strDt)).ToString("yyyy-MM-ddTHH:mm:ssZ");
        }

        public bool  MigrateData(DataTable oResultData, Hashtable oColMapping, Hashtable oListMapping, string Weburl, ProgressBar progressBar, ListView lstView, ImageList imgLst,Label lblTimer, Stopwatch objWatch)
        {
            try
            {
                lviMap = lstView.Items.Add("Mapping Name :    '" + _lmapName + "'");
                lstView.Items[lstView.Items.Count - 1].BackColor = Color.Gray;
                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.White;
                lstView.Items[lstView.Items.Count - 1].Font = new Font(lstView.Font, FontStyle.Bold);
                using (ClientContext ctx = new ClientContext(Weburl))
                {
                    foreach (string destlist in oListMapping.Keys)
                    {
                        _ldstLst = destlist;
                        List olist = ctx.Web.Lists.GetByTitle(destlist);
                        Web oweb = ctx.Web;
                        ctx.Load(oweb);
                        ctx.Load(olist);
                        ctx.ExecuteQuery();
                        if (oResultData != null)
                        {
                            progressBar.Maximum = progressBar.Value + (oResultData.Rows.Count * MapFldCnt);
                            if (oResultData.Rows.Count > 0)
                            {
                                foreach (DataRow dtrow in oResultData.Rows)
                                {
                                    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                                    ListItem oListItem = olist.AddItem(itemCreateInfo);
                                    foreach (string destfld in oColMapping.Keys)
                                    {
                                        _lsrcTbl = ""; _ldstLst = ""; _ltblFld = ""; _llstFld = ""; _litemUrl = "";
                                        lblTimer.Text = Convert.ToString(objWatch.Elapsed);

                                        if (destfld.Split('.')[2] == destlist)
                                        {


                                        }
                                       
                                            if (destfld.Split('.')[0] == destlist)
                                            {
                                            progressBar.PerformStep();
 #region "Time"
 //                                           string tim = Convert.ToString(Convert.ToString(oColMapping[destfld]).Split('.')[3]);
                                           
 //                                               if (destfld.Split('.')[2] == "DateTime" && tim.Contains("String"))
 //                                               {
 //                                                   try
 //                                                   {
 //                                                       tim = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
 //                                                       tim= tim.Remove(5);
 //                                                       //tim = ToSPDateTime(tim);

 //                                                       oListItem[destfld.Split('.')[1]] = tim; // DateTime.Now.ToString(DateTimeFormatInfo.InvariantInfo);
                                                  
 //                                                   }
 //                                                   catch (Exception ex)
 //                                                   {
 //                                                       MessageBox.Show(ex.Message);
 //                                                       GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Date Conversion Issue ::" + Convert.ToString(oColMapping[destfld]).Split('.')[1] + ex.Message, MappingMode);
 //                                                   }

 //                                               }
                                            #endregion

 #region "Date"
                                            //string tim1 = Convert.ToString(Convert.ToString(oColMapping[destfld]).Split('.')[2]);
                                                if (destfld.Split('.')[2] == "DateTime")
                                                {
                                                    try
                                                    {
                                                
                                                        strDt = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                        //strDts = ToSPDate(strDt);
                                                        strDts = ToSPDateTime(strDt);
                                                 
                                                        if (!string.IsNullOrEmpty(strDts) || !string.IsNullOrWhiteSpace(strDts))
                                                        {
                                                           //DateTime odtvalue = DateTime.Parse(Convert.ToString(strDt), System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
                                                           //oListItem[destfld.Split('.')[1]] = odtvalue;
                                                           //DateTime odtvalue = DateTime.Parse(Convert.ToString(strDt), System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
                                                            oListItem[destfld.Split('.')[1]] = strDts;
                                                        }
                                                        else
                                                        { 
                                                     
                                                        }
                                                    }
                                                    catch (Exception ex)
                                                    {
                                                        MessageBox.Show(ex.Message);
                                                       // Generate Exception Log this field is not updated because Date time conversion Not Happening
                                                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Date Conversion Issue ::"+Convert.ToString(oColMapping[destfld]).Split('.')[1]+ ex.Message , MappingMode);
                                                    }

                                                }
 #endregion

 #region "Choice"
                                            else if (destfld.Split('.')[2] == "Choice")
                                            {
                                                try
                                                {

                                                    string choiceFld = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                    if (!string.IsNullOrEmpty(choiceFld) || !string.IsNullOrWhiteSpace(choiceFld))
                                                    {
                                                        oListItem[destfld.Split('.')[1]] = choiceFld;
                                                    }
                                                    else
                                                    {

                                                    }

                                                }
                                                catch (Exception ex)
                                                {
                                                    MessageBox.Show(ex.Message);
                                                    // Generate Exception Log this field is not updated because Date time conversion Not Happening
                                                    GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Date Conversion Issue ::" + Convert.ToString(oColMapping[destfld]).Split('.')[1] + ex.Message, MappingMode);
                                                }

                                            }
#endregion

                                            else
                                            {
                                                string uptFld = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                if (!string.IsNullOrEmpty(uptFld))
                                                {
                                                    oListItem[destfld.Split('.')[1]] = uptFld;
                                                }
                                                else
                                                { 
                                                
                                                }
                                                //oListItem[destfld.Split('.')[1]] = dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]];
                                            }
                                            oListItem.Update();
                                            ctx.ExecuteQuery();
                                            ctx.Load(oListItem.ParentList);
                                            ctx.ExecuteQuery();
                                            _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                            _ldstLst = destlist;
                                            _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                            _llstFld = destfld.Split('.')[1];
                                            itemID = GetID(webURL, destlist);
                                            _litemUrl = oListItem.ParentList.DefaultDisplayFormUrl + "?ID=" + itemID;
                                            //_litemUrl = webURL + olist.RootFolder.ServerRelativeUrl + "/DispForm.aspx?ID=" + itemID;
                                            lvi1 = lstView.Items.Add(Convert.ToString("               Source :   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld +"'"));
                                            lvi1.ImageIndex = 2;
                                            
                                            string str = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);

#region "Check the value is empty or null,depending upon the log is generated"
                                            if (string.IsNullOrEmpty(str))
                                            {
                                                string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                _ltblFld = _ltblFld  + " Refer Source field value contain: " + rowVal;
                                                lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                lvi2.ImageIndex = 4;
                                                lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                                lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                                                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
                                            }
                                            else
                                            {
                                            GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully", MappingMode);
                                            }
#endregion


                                        }
                                    }
                                }
                                status = true;
                            }
                            else
                            {
                                lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                lvi2.ImageIndex = 4;
                                lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully But No Records found for your query", MappingMode);
                                //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", ex.Message,MappingMode);
                MessageBox.Show(ex.Message);
            }
            return status;
        }

        /// <summary>
        /// Migrate the records in the Sharepoint Library
        /// </summary>
        /// <param name="oResultData">Oracle Table Result Set</param>
        /// <param name="oColMapping">Mapping Field Details</param>
        /// <param name="oListMapping">Mapping Library Details</param>
        /// <param name="Weburl">Site Url</param>
        /// <param name="progressBar">Progress Bar to check the completion of the process</param>
       
        //public void MigrateToLibrary(DataTable oResultData, Hashtable oColMapping, Hashtable oListMapping, string Weburl, ProgressBar progressBar, ListView lstView)
        //{
        //    try
        //    {
        //        lviMap = lstView.Items.Add("Mapping Name :    '" + _lmapName + "'");
        //        lstView.Items[lstView.Items.Count - 1].BackColor = Color.Gray;
        //        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.White;
        //        lstView.Items[lstView.Items.Count - 1].Font = new Font(lstView.Font, FontStyle.Bold);
        //        using (ClientContext ctx = new ClientContext(Weburl))
        //        {
        //            foreach (string destlist in oListMapping.Keys)
        //            {
        //                _ldstLst = destlist;
        //                List olist = ctx.Web.Lists.GetByTitle(destlist);
        //                ctx.Load(olist.RootFolder);
        //                ctx.Load(olist);
        //                ctx.ExecuteQuery();
        //                Web oweb = ctx.Web;
        //                if (oResultData != null)
        //                {
        //                    progressBar.Maximum = progressBar.Value + (oResultData.Rows.Count * MapFldCnt);
        //                    if (oResultData.Rows.Count > 0)
        //                    {
        //                        foreach (DataRow dtrow in oResultData.Rows)
        //                        {
        //                            foreach (string destfld in oColMapping.Keys)
        //                            {
        //                                _lsrcTbl = ""; _ldstLst = ""; _ltblFld = ""; _llstFld = ""; _litemUrl = "";
        //                                if (destfld.Split('.')[0] == destlist)
        //                                {
        //                                    progressBar.PerformStep();
        //                                    string filePath = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
        //                                    string destpath = "";
        //                                    destpath = olist.RootFolder.ServerRelativeUrl + "/" + Path.GetFileName(filePath);
        //                                    using (FileStream fs = new FileStream(filePath, FileMode.Open))
        //                                    {
        //                                        Microsoft.SharePoint.Client.File.SaveBinaryDirect(ctx, destpath, fs,true);
        //                                        ctx.ExecuteQuery();
        //                                        _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
        //                                        _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
        //                                        _llstFld = destfld.Split('.')[1];
        //                                        itemID = GetID(webURL, destlist);
        //                                        _litemUrl = webURL + olist.DefaultDisplayFormUrl + "?ID=" + itemID;
        //                                        lvi1 = lstView.Items.Add(Convert.ToString("Source Table:   " + _lsrcTbl + "   Destination List:   " + _ldstLst + "   Table Field:   " + _ltblFld + "   Destination List Field:   " + _llstFld));
        //                                        lvi1.ImageIndex = 2;
        //                                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully");
        //                                    }
        //                                }
        //                            }
        //                        }
        //                        MessageBox.Show("Migration Completed Successfully");
        //                    }
        //                    else
        //                    {
        //                        lvi1.ImageIndex = 4;
        //                        lvi1 = lstView.Items.Add(Convert.ToString("Source Table:   " + _lsrcTbl + "   Destination List:   " + _ldstLst + "   Table Field:   " + _ltblFld + "   Destination List Field:   " + _llstFld));
        //                        //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
        //                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully But No Records found for you");
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        lvi1 = lstView.Items.Add(Convert.ToString("Source Table:   " + _lsrcTbl + "   Destination List:   " + _ldstLst + "   Table Field:   " + _ltblFld + "   Destination List Field:   " + _llstFld));
        //        lvi1.ImageIndex = 3;
        //        lvi1 = lstView.Items.Add(Convert.ToString("File not found at the given target" + ex.Message));
        //        lvi1.ImageIndex = 3;
        //        lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
        //        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
        //        //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
        //        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", ex.Message);
        //        MessageBox.Show("File not found at the given target" + ex.Message);
        //    }
        //}

        public void MigrateToLibrary(DataTable oResultData, Hashtable oColMapping, Hashtable oListMapping, string Weburl, ProgressBar progressBar, ListView lstView)
        {
            try
            {
                lviMap = lstView.Items.Add("Mapping Name :    '" + _lmapName + "'");
                lstView.Items[lstView.Items.Count - 1].BackColor = Color.Gray;
                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.White;
                lstView.Items[lstView.Items.Count - 1].Font = new Font(lstView.Font, FontStyle.Bold);
                using (ClientContext ctx = new ClientContext(Weburl))
                {
                    foreach (string destlist in oListMapping.Keys)
                    {
                        _ldstLst = destlist;
                        List olist = ctx.Web.Lists.GetByTitle(destlist);
                        ctx.Load(olist.RootFolder);
                        ctx.Load(olist);
                        ctx.ExecuteQuery();
                        Web oweb = ctx.Web;

                        if (oResultData != null)
                        {
                            progressBar.Maximum = progressBar.Value + (oResultData.Rows.Count * MapFldCnt);
                            if (oResultData.Rows.Count > 0)
                            {
                                foreach (DataRow dtrow in oResultData.Rows)
                                {
                                    fileExists = false;
                                    Microsoft.SharePoint.Client.File uploadFile = null;
                                    foreach (string destfld in oColMapping.Keys)
                                    {
                                        _lsrcTbl = ""; _ldstLst = ""; _ltblFld = ""; _llstFld = ""; _litemUrl = "";
                                        if (destfld.Split('.')[0] == destlist)
                                        {
                                            if (string.Equals(destfld.Split('.')[1], "FileLeafRef"))
                                            {
                                                progressBar.PerformStep();
                                                string filePath1 = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                if (!string.IsNullOrEmpty(filePath1))
                                                {
                                                    try
                                                    {
                                                        FileCreationInformation newFile = new FileCreationInformation();
                                                        newFile.Content = System.IO.File.ReadAllBytes(filePath1);
                                                        FileHelper obj = new FileHelper(filePath1);
                                                        newFile.Url = obj.FileName + obj.Extension;
                                                        List docs = oweb.Lists.GetByTitle(destfld.Split('.')[0]);
                                                        uploadFile = docs.RootFolder.Files.Add(newFile);
                                                        ctx.Load(uploadFile);
                                                        ctx.ExecuteQuery();
                                                        fileExists = true;
                                                        string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                        //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);

                                                        _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                                        _ldstLst = destlist;
                                                        _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                                        _llstFld = destfld.Split('.')[1];

                                                        itemID = GetID(webURL, destlist);
                                                        _litemUrl = olist.DefaultViewUrl  + "?ID=" + itemID;
                                                        string filepath = olist.DefaultViewUrl;//+ "/" + olist.Url;
                                                        _ltblFld = _ltblFld + " Refer Source field value contain: " + rowVal;
                                                        lvi2 = lstView.Items.Add(Convert.ToString(" Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                        lvi2.ImageIndex = 0;
                                                        lvi2 = lstView.Items.Add("               Records found for your Mapping");
                                                        lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Green;
                                                        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Green;
                                                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully", MappingMode);
                                                    }
                                                    catch (Exception ee)
                                                    {

                                                        string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                        //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                        _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                                        _ldstLst = destlist;
                                                        _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                                        _llstFld = destfld.Split('.')[1];
                                                        _ltblFld = _ltblFld + ":  ReferSourcefield value : " + rowVal + "File already Exist";
                                                        lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                        lvi2.ImageIndex = 4;
                                                        // lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                                        lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Gray;
                                                        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Gray;
                                                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
                                                        filePrb = true;
                                                    }
                                                   
                                                }
                                                if (!filePrb)
                                                {
                                                    string rowVal1 = Convert.ToString(dtrow.ItemArray[0]);
                                                    //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                    _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                                    _ldstLst = destlist;
                                                    _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                                    _llstFld = destfld.Split('.')[1];
                                                    _ltblFld = _ltblFld + ":  ReferSourcefield value : " + rowVal1;
                                                    lvi2 = lstView.Items.Add(Convert.ToString("    Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                    lvi2.ImageIndex = 4;
                                                    lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                                    lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                                                    lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                                    GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
                                                    filePrb = true;
                                                }

                                           }

                                            else
                                            {
                                                if (!fileExists)
                                                {
                                                    string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                    //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                    _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                                    _ldstLst = destlist;
                                                    _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                                    _llstFld = destfld.Split('.')[1];
                                                    _ltblFld = _ltblFld + ":   ReferSourcefield value : " + rowVal;
                                                    lvi2 = lstView.Items.Add(Convert.ToString("     Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                    lvi2.ImageIndex = 4;
                                                    lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                                    lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                                                    lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                                    GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
                                                }
                                               

                                                foreach (string destflds in oColMapping.Keys)
                                                {

                                                    if (string.Equals(destflds.Split('.')[1], "FileLeafRef"))
                                                    {


                                                    }
                                                    else
                                                    {
                                                        if (fileExists)
                                                        {
                                                            ListItem item = uploadFile.ListItemAllFields;

                                                            if (destfld.Split('.')[2] == "DateTime")
                                                            {
                                                                strDt = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                                //strDts = ToSPDate(strDt);
                                                                strDts = ToSPDateTime(strDt);

                                                                if (!string.IsNullOrEmpty(strDts) || !string.IsNullOrWhiteSpace(strDts))
                                                                {
                                                                    //DateTime odtvalue = DateTime.Parse(Convert.ToString(strDt), System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
                                                                    //oListItem[destfld.Split('.')[1]] = odtvalue;
                                                                    //DateTime odtvalue = DateTime.Parse(Convert.ToString(strDt), System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
                                                                    item[destfld.Split('.')[1]] = strDts;
                                                                    fldExist = true;
                                                                }
                                                                else
                                                                {
                                                                    fldExist = false;
                                                                }
                                                              
                                                            }


                                                            else if (destfld.Split('.')[2] == "Choice")
                                                            {
                                                                try
                                                                {

                                                                    string choiceFld = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                                    if (!string.IsNullOrEmpty(choiceFld) || !string.IsNullOrWhiteSpace(choiceFld))
                                                                    {
                                                                        item[destfld.Split('.')[1]] = choiceFld;
                                                                        fldExist = true;
                                                                    }
                                                                    else
                                                                    {
                                                                        fldExist = false;
                                                                    }

                                                                }
                                                                catch (Exception ex)
                                                                {
                                                                    MessageBox.Show(ex.Message);
                                                                    // Generate Exception Log this field is not updated because Date time conversion Not Happening
                                                                    GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Date Conversion Issue ::" + Convert.ToString(oColMapping[destfld]).Split('.')[1] + ex.Message, MappingMode);
                                                                }

                                                            }

                                                            else
                                                            {
                                                                string uptFld = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                                if (!string.IsNullOrEmpty(uptFld))
                                                                {
                                                                    item[destfld.Split('.')[1]] = uptFld;
                                                                    fldExist = true;
                                                                }
                                                                else
                                                                {
                                                                    fldExist = false;

                                                                }
                                                                
                                                                //item[destfld.Split('.')[1]] = dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]];
                                                            }
                                                           
                                                            # region "Log"
                                                            //string str = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                            //if (string.IsNullOrEmpty(str))
                                                            //{
                                                            //    string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                            //    //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                            //    _ltblFld = _ltblFld + " Refer Source field value contain: " + rowVal;
                                                            //    lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                            //    lvi2.ImageIndex = 4;
                                                            //    lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                                            //    lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                                                            //    lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                                            //    GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
                                                            //}
                                                            //else
                                                            //{
                                                            //    string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                            //   // _ltblFld = _ltblFld + " Refer Source field value contain: " + rowVal;
                                                            //    lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                            //    lvi2.ImageIndex = 4;
                                                            //    lvi2 = lstView.Items.Add("               Records found for your Mapping");
                                                            //    lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Green;
                                                            //    lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Green;
                                                               
                                                            //    GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully", MappingMode);
                                                            //}
#endregion
                                                            if (!fldExist)
                                                            {
                                                                string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                                //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                                _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                                                _ldstLst = destlist;
                                                                _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                                                _llstFld = destfld.Split('.')[1];
                                                                _ltblFld = _ltblFld + ":   ReferSourcefield value : " + rowVal;
                                                                lvi2 = lstView.Items.Add(Convert.ToString("     Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                                lvi2.ImageIndex = 4;
                                                                lvi2 = lstView.Items.Add("               No Records found for your Mapping");
                                                                lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                                                                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                                                                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
                                                            }
                                                            else
                                                            {
                                                                string rowVal = Convert.ToString(dtrow.ItemArray[0]);
                                                                // _ltblFld = _ltblFld + " Refer Source field value contain: " + rowVal;
                                                                _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
                                                                _ldstLst = destlist;
                                                                _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
                                                                _llstFld = destfld.Split('.')[1];
                                                                lvi2 = lstView.Items.Add(Convert.ToString("      Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
                                                                lvi2.ImageIndex = 0;
                                                                lvi2 = lstView.Items.Add("               Records found for your Mapping");
                                                                lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Green;
                                                                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Green;

                                                                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully", MappingMode);
                                                            }

                                                            item.Update();
                                                            ctx.ExecuteQuery();
                                                        }

                                                        
                                                        //string str = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
                                                       
                                                    }

                                                }

                                            }
                                               
                      
                                        }

                                        //GenerateLog(_lmapName, dtrow[0].ToString(), _ldstLst, _ltblFld, _llstFld, _litemUrl, "Null URL Field", "Field Migrated not contain But No Records found for you", MappingMode);
                                    }

                                }
                                MessageBox.Show("Migration Completed Successfully");
                            }
                            else
                            {
                                lvi1.ImageIndex = 4;
                                lvi1 = lstView.Items.Add(Convert.ToString("Source:   " + _lsrcTbl + "   Destination List:   " + _ldstLst + "   Table Field:   " + _ltblFld + "   Destination List Field:   " + _llstFld));
                                //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
                                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully But No Records found for you", MappingMode);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                lvi1 = lstView.Items.Add(Convert.ToString("Source:   " + _lsrcTbl + "   Destination List:   " + _ldstLst + "   Table Field:   " + _ltblFld + "   Destination List Field:   " + _llstFld));
                lvi1.ImageIndex = 3;
                lvi1 = lstView.Items.Add(Convert.ToString("File not found at the given target" + ex.Message));
                lvi1.ImageIndex = 3;
                lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
                lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
                //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
                GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", ex.Message, MappingMode);
                //MessageBox.Show("File not found at the given target" + ex.Message);
            }
        }

        //public bool MigrateData_ex(DataTable oResultData, Hashtable oColMapping, Hashtable oListMapping, string Weburl, ProgressBar progressBar, ListView lstView, ImageList imgLst, Label lblTimer, Stopwatch objWatch)
        //{
        //    try
        //    {
        //        lviMap = lstView.Items.Add("Mapping Name :    '" + _lmapName + "'");
        //        lstView.Items[lstView.Items.Count - 1].BackColor = Color.Gray;
        //        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.White;
        //        lstView.Items[lstView.Items.Count - 1].Font = new Font(lstView.Font, FontStyle.Bold);
        //        using (ClientContext ctx = new ClientContext(Weburl))
        //        {
        //            foreach (string destlist in oListMapping.Keys)
        //            {
        //                _ldstLst = destlist;
        //                List olist = ctx.Web.Lists.GetByTitle(destlist);
        //                Web oweb = ctx.Web;
        //                ctx.Load(oweb);
        //                ctx.Load(olist);
        //                ctx.ExecuteQuery();
        //                if (oResultData != null)
        //                {
        //                    progressBar.Maximum = progressBar.Value + (oResultData.Rows.Count * MapFldCnt);
        //                    if (oResultData.Rows.Count > 0)
        //                    {
        //                        foreach (DataRow dtrow in oResultData.Rows)
        //                        {
        //                            ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
        //                            ListItem oListItem = olist.AddItem(itemCreateInfo);
        //                            Microsoft.SharePoint.Client.File uploadFile = null;
        //                            foreach (string destfld in oColMapping.Keys)
        //                            {
        //                                _lsrcTbl = ""; _ldstLst = ""; _ltblFld = ""; _llstFld = ""; _litemUrl = "";
        //                                lblTimer.Text = Convert.ToString(objWatch.Elapsed);

        //                                if (destfld.Split('.')[2] == destlist)
        //                                {


        //                                }

        //                                if (destfld.Split('.')[0] == destlist)
        //                                {
        //                                    progressBar.PerformStep();
                                            

        //                                    if (string.Equals(destfld.Split('.')[1], "FileLeafRef"))
        //                                    {
        //                                        progressBar.PerformStep();
        //                                        string filePath1 = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
        //                                        if (!string.IsNullOrEmpty(filePath1))
        //                                        {
        //                                            FileCreationInformation newFile = new FileCreationInformation();
        //                                            newFile.Content = System.IO.File.ReadAllBytes(filePath1);
        //                                            FileHelper obj = new FileHelper(filePath1);
        //                                            newFile.Url = obj.FileName + obj.Extension;

        //                                            List docs = oweb.Lists.GetByTitle(destfld.Split('.')[0]);
        //                                            uploadFile = docs.RootFolder.Files.Add(newFile);
        //                                            ctx.Load(uploadFile);
        //                                            ctx.ExecuteQuery();

        //                                            fileExists = true;

        //                                        }

        //                                    }

        //                                    #region "Date"
        //                                    //string tim1 = Convert.ToString(Convert.ToString(oColMapping[destfld]).Split('.')[2]);
        //                                    else if (destfld.Split('.')[2] == "DateTime")
        //                                    {
        //                                        try
        //                                        {

        //                                            strDt = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
        //                                            //strDts = ToSPDate(strDt);
        //                                            strDts = ToSPDateTime(strDt);

        //                                            if (!string.IsNullOrEmpty(strDts) || !string.IsNullOrWhiteSpace(strDts))
        //                                            {
        //                                                //DateTime odtvalue = DateTime.Parse(Convert.ToString(strDt), System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
        //                                                //oListItem[destfld.Split('.')[1]] = odtvalue;
        //                                                //DateTime odtvalue = DateTime.Parse(Convert.ToString(strDt), System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
        //                                                oListItem[destfld.Split('.')[1]] = strDts;
        //                                            }
        //                                            else
        //                                            {

        //                                            }
        //                                        }
        //                                        catch (Exception ex)
        //                                        {
        //                                            MessageBox.Show(ex.Message);
        //                                            // Generate Exception Log this field is not updated because Date time conversion Not Happening
        //                                            GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Date Conversion Issue ::" + Convert.ToString(oColMapping[destfld]).Split('.')[1] + ex.Message, MappingMode);
        //                                        }

        //                                    }
        //                                    #endregion

        //                                    #region "Choice"
        //                                    else if (destfld.Split('.')[2] == "Choice")
        //                                    {
        //                                        try
        //                                        {

        //                                            string choiceFld = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
        //                                            if (!string.IsNullOrEmpty(choiceFld) || !string.IsNullOrWhiteSpace(choiceFld))
        //                                            {
        //                                                oListItem[destfld.Split('.')[1]] = choiceFld;
        //                                            }
        //                                            else
        //                                            {

        //                                            }

        //                                        }
        //                                        catch (Exception ex)
        //                                        {
        //                                            MessageBox.Show(ex.Message);
        //                                            // Generate Exception Log this field is not updated because Date time conversion Not Happening
        //                                            GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Date Conversion Issue ::" + Convert.ToString(oColMapping[destfld]).Split('.')[1] + ex.Message, MappingMode);
        //                                        }

        //                                    }
        //                                    #endregion

        //                                    else
        //                                    {
        //                                        oListItem[destfld.Split('.')[1]] = dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]];
        //                                    }

        //                                    if (string.Equals(destfld.Split('.')[1], "FileLeafRef"))
        //                                    {
        //                                    }
        //                                    else
        //                                    {
        //                                        oListItem.Update();
        //                                        ctx.ExecuteQuery();
        //                                        ctx.Load(oListItem.ParentList);
        //                                        ctx.ExecuteQuery();
        //                                        _lsrcTbl = Convert.ToString(oColMapping[destfld]).Split('.')[0];
        //                                        _ldstLst = destlist;
        //                                        _ltblFld = Convert.ToString(oColMapping[destfld]).Split('.')[1];
        //                                        _llstFld = destfld.Split('.')[1];
        //                                        itemID = GetID(webURL, destlist);
        //                                        _litemUrl = oListItem.ParentList.DefaultDisplayFormUrl + "?ID=" + itemID;
        //                                        //_litemUrl = webURL + olist.RootFolder.ServerRelativeUrl + "/DispForm.aspx?ID=" + itemID;
        //                                        lvi1 = lstView.Items.Add(Convert.ToString("               Source :   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
        //                                        lvi1.ImageIndex = 2;
        //                                    }

        //                                    string str = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);

        //                                    #region "Check the value is empty or null,depending upon the log is generated"
        //                                    if (string.IsNullOrEmpty(str))
        //                                    {
        //                                        string rowVal = Convert.ToString(dtrow.ItemArray[0]);
        //                                        //string rowVal = Convert.ToString(dtrow[Convert.ToString(oColMapping[destfld]).Split('.')[1]]);
        //                                        _ltblFld = _ltblFld + " Refer Source field value contain: " + rowVal;
        //                                        lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
        //                                        lvi2.ImageIndex = 4;
        //                                        lvi2 = lstView.Items.Add("               No Records found for your Mapping");
        //                                        lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
        //                                        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
        //                                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", "Field dosn't contain Value", MappingMode);
        //                                    }
        //                                    else
        //                                    {
        //                                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully", MappingMode);
        //                                    }
        //                                    #endregion


        //                                }
        //                            }
        //                        }
        //                        status = true;
        //                    }
        //                    else
        //                    {
        //                        lvi2 = lstView.Items.Add(Convert.ToString("               Source:   '" + _lsrcTbl + "'   Destination List:   '" + _ldstLst + "'   Table Field:   '" + _ltblFld + "'   Destination List Field:   '" + _llstFld + "'"));
        //                        lvi2.ImageIndex = 4;
        //                        lvi2 = lstView.Items.Add("               No Records found for your Mapping");
        //                        lstView.Items[lstView.Items.Count - 2].ForeColor = Color.Red;
        //                        lstView.Items[lstView.Items.Count - 1].ForeColor = Color.Red;
        //                        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Success", "Field Migrated Successfully But No Records found for your query", MappingMode);
        //                        //MessageBox.Show("No Records Found in " + mapColumns.srcTableName + " Table ");
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        GenerateLog(_lmapName, _lsrcTbl, _ldstLst, _ltblFld, _llstFld, _litemUrl, "Failure", ex.Message, MappingMode);
        //        MessageBox.Show(ex.Message);
        //    }
        //    return status;
        //}

        private string GetDocID(string webURL, string destlist)
        {
            using (ClientContext cliContext = new ClientContext(webURL))
            {
                Web web = cliContext.Web;
                List list = web.Lists.GetByTitle(destlist);
                cliContext.Load(list);
                cliContext.ExecuteQuery();
                CamlQuery query = new CamlQuery();
                query.ViewXml = "<View><Query> <Where><Eq><FieldRef Name='FileLeafRef' /> <Value Type='File'>"+destlist+"</Value></Eq></Where></Query></View>";
                ListItemCollection itmColl = list.GetItems(query);
                cliContext.Load(itmColl);
                cliContext.ExecuteQuery();
                int count = itmColl.Count - 1;
                string itemID = Convert.ToString(itmColl[count]["ID"]);
                return itemID;
            }
        }

        /// <summary>
        /// Used to get the ID of last added item
        /// </summary>
        /// <param name="webURL"></param>
        /// <param name="destlist"></param>
        /// <returns></returns>
        /// 

        private string GetID(string webURL, string destlist)
        {
            using (ClientContext cliContext = new ClientContext(webURL))
            {
                Web web = cliContext.Web;
                List list = web.Lists.GetByTitle(destlist);
                cliContext.Load(list);
                cliContext.ExecuteQuery();
                CamlQuery query = new CamlQuery();
                query.ViewXml = "<View><Query><OrderBy><FieldRef Name='Created' Ascending='True' /></OrderBy></Query></View>";
                ListItemCollection itmColl = list.GetItems(query);
                cliContext.Load(itmColl);
                cliContext.ExecuteQuery();
                int count = itmColl.Count - 1;
                string itemID = Convert.ToString(itmColl[count]["ID"]);
                return itemID;
            }
        }

        /// <summary>
        /// Helps to get the subsite web url
        /// </summary>
        /// <param name="weburl">passing the site url</param>
        /// <returns></returns>
        /// 

        private Web getWeb(string weburl)
        {
            Web oweb = null;
            try
            {
                using (ClientContext ctx = new ClientContext(weburl))
                {
                    oweb = ctx.Web;
                    ctx.Load(oweb);
                    ctx.ExecuteQuery();
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return oweb;
        }

        /// <summary>
        /// Generate Entries for the Migration 
        /// </summary>
        /// <param name="srcTbl"></param>
        /// <param name="destLst"></param>
        /// <param name="tblFld"></param>
        /// <param name="LstFld"></param>
        /// <param name="itemUrl"></param>
        /// <param name="status"></param>
        /// <param name="comments"></param>
        /// <returns></returns>
        /// 

        public void GenerateLog(string mapname, string srcTbl, string destLst, string tblFld, string LstFld, string itemUrl, string status, string comments,string _mappingMode)
        {
            using (ClientContext cliContext = new ClientContext(SiteUrl))
            {
                Web web = cliContext.Web;
                List list = web.Lists.GetByTitle("Migration Log");
                cliContext.Load(list);
                cliContext.ExecuteQuery();
                ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                ListItem item = list.AddItem(itemCreateInfo);
                
                item["MappingName"] = mapname;
                item["Title"] = srcTbl;
                item["DestList"] = destLst;
                item["SrcTableField"] = tblFld;
                item["DestListField"] = LstFld;
                item["ItemUrl"] = itemUrl;
                item["Status"] = status;
                item["Comments"] = comments;
                item["MappingMode"] = _mappingMode;
                item.Update();
                cliContext.ExecuteQuery();
            }
        }

        public int GetMapRecordCnt(MappingListValues oMapVal)
        {
            ohtListName = new Hashtable();
            ohtfldMapping = new Hashtable();
            ohtDocMapping = new Hashtable();
            int cnt = 0;
            try
            {
                using (ClientContext ctx = new ClientContext(SiteUrl))
                {
                    List list = ctx.Web.Lists.GetByTitle("Mapping Column List");
                    CamlQuery query = new CamlQuery();
                    query.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID'/><Value Type='Text'>" + Convert.ToString(oMapVal.Itemid) + "</Value></Eq></Where></Query></View>";
                    ListItemCollection listItems = list.GetItems(query);
                    ctx.Load(listItems);
                    ctx.ExecuteQuery();
                    MapFldCnt = listItems.Count;
                    foreach (var lstItem in listItems)
                    {
                        FieldUrlValue oweburlval = (FieldUrlValue)lstItem["Web_Url"];
                        webURL = oweburlval.Url;
                        mapColumns.srcTableName = Convert.ToString(lstItem["Source_Table_Name"]);
                        mapColumns.destListName = Convert.ToString(lstItem["Destination_List_Name"]);
                        mapColumns.ListType = Convert.ToString(lstItem["Destination_List_Template"]);
                        if (!ohtListName.ContainsKey(mapColumns.destListName))
                            ohtListName.Add(mapColumns.destListName, mapColumns.destListName);
                        if (string.Equals(mapColumns.ListType, "DocumentLibrary"))
                        {
                            chkDoc = true;
                            //ohtDocMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"]));
                            ohtDocMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]) + "." + Convert.ToString(lstItem["Destination_Field_Type"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"] + "." + Convert.ToString(lstItem["Source_Field_Type"])));
                            
                        }
                        else
                        {
                            //ohtfldMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"]));
                            ohtfldMapping.Add(mapColumns.destListName + "." + Convert.ToString(lstItem["Destination_Field"]) + "." + Convert.ToString(lstItem["Destination_Field_Type"]), mapColumns.srcTableName + "." + Convert.ToString(lstItem["Source_Field"] + "." + Convert.ToString(lstItem["Source_Field_Type"])));
                        }
                    }

                    if (string.IsNullOrEmpty(oMapVal.filePath))
                    {
                        dtResults = RetrieveData(mapColumns.srcTableName, Convert.ToString(oMapVal.Query), Convert.ToString(oMapVal.AllItems));
                    }
                    else
                    {
                        dtResults = RetrieveData(oMapVal.filePath);
                    }

                }
                cnt = MapFldCnt * dtResults.Rows.Count;

            }
                 
            catch(Exception ee)
            {
                //MessageBox.Show(ee.Message);
                cnt = 0;
            }
            return  cnt;
        }

        public void Delete_List_Records()
        {

            using (ClientContext clientContext = new ClientContext(SiteUrl))
            {

                // Request List
                List oList = clientContext.Web.Lists.GetByTitle("Migration Log");
                clientContext.ExecuteQuery();
                                
                // Request Items in list a empty query is all you need
                ListItemCollection items = oList.GetItems(new CamlQuery());
                // Load the Listitems from server
                clientContext.Load(items);
                // Execute the query
                clientContext.ExecuteQuery();
                // Do what ever you want with the item count or the list items itself
                //int itemCount = items.Count;

                ArrayList idArr = new ArrayList();

                foreach (ListItem li in items)
                {
                    idArr.Add(li["ID"]);
                }

                foreach (int val in idArr)
                {
                    using (var clictx = new ClientContext(SiteUrl))
                    {
                        var list = clictx.Web.Lists.GetByTitle("Migration Log");
                        var product = list.GetItemById(val);
                        product.DeleteObject();
                        clictx.ExecuteQuery();
                    }
                }
                         
            }

            MessageBox.Show("Log Regords Deleted Sucessfully");
        }

    }
}
